﻿<?php
// No direct access to this file
defined('_JEXEC') or die('Restricted access');
 
// import Joomla modelitem library
jimport('joomla.application.component.modelitem');
 
/**
 * Retail Shop  Model
 */

 class RetailShopModelRetailShop extends JModelItem{
    
//--  check extent file
	function check_extent_file($file_name, $extent_file) {
		$pattern = '/.+\.(' . $extent_file . ')$/i';
		if (preg_match($pattern, $file_name)) {
			$file_type = true;
		} else {
			$file_type = false;
		}
		return $file_type;
	}	
	
//-- -----------------------------------------------------
//-- 1. Table `#__retailshop_brand`
//-- -----------------------------------------------------
	// get all brand
	public function getArrBrand(){        
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_brand as h')			
			->select('*')
			->where('status = 1'));
		if (!$this->_db->loadObjectList()) {
				$this->setError($this->_db->getError());
		}		
		return $this->_db->loadObjectList();
	}
	// add new
	public function addBrand($name){
		$db =& JFactory::getDBO();
		$query = "SELECT id FROM #__retailshop_brand WHERE name = ".$db->Quote($name)." LIMIT 0,1";
		$db->setQuery($query);
		$id = $db->loadObject();		
		
		if($id == null){
			$query = 'INSERT INTO #__retailshop_brand(name, `order`, status, updated) values ('.$db->Quote($name).', 1, 1, CURDATE())';
			
			$db->setQuery($query);
			if($db->query()){						
				$query = "SELECT * FROM #__retailshop_brand WHERE name = ".$db->Quote($name)." LIMIT 0,1";
				$id = $db->loadObject();
				return $id->id;
			} else {					
				$this->last_error = $db->getErrorMsg();
				return false;
			}
		}else {
			return $id->id;
		}
	}
	//edit
	public function editBrand($id, $name, $order = 1, $status = 1){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_brand SET name = '.$db->Quote($name).', `order`= '.$db->Quote($order).', status = '.$db->Quote($status).', updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	
	public function deleteBrand($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_brand WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
//-- -----------------------------------------------------
//-- 2. Table `#__retailshop_category`
//-- -----------------------------------------------------
	public function getCategory(){   	
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_category as h')			
			->select('*')
			->where('status = 1'));
		if (!$this->_db->loadObjectList()) {
				$this->setError($this->_db->getError());
		}
		return $this->_db->loadObjectList();
	}
	//get category by administrator
	public function getCategorybyAdmin($arr = null){
		$db =& JFactory::getDBO();
		$query = 'SELECT * FROM #__retailshop_category WHERE id IN(';		
		if(is_array($arr)){
			for($i = 0; $i < count($arr); $i++){
				$query .=  ($i==0)?$arr[$i]:','.$arr[$i];
			}
		}
		$query .= ')';		
		$db->setQuery($query);
		return $db->loadObjectList();
	}
	
	// add new
	public function addCategory($name, $order = 1, $status = 1){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_category(name, `order`, status, updated) values ('.
					$db->Quote($name).','.
					$db->Quote($order).','.
					$db->Quote($status).','.
					'CURDATE())';
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//edit
	public function editCategory($id,$name, $order = 1, $status = 1){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_category SET name = '.$db->Quote($name).', `order`= '.$db->Quote($order).', status = '.$db->Quote($status).', updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//delete
	public function deleteCategory($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_category WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
//-- -----------------------------------------------------
//-- 3. Table `#__retailshop_country`
//-- -----------------------------------------------------
	public function getArrCountry(){        
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_country as h')			
			->select('*')
			->where('status = 1'));
		if (!$this->_db->loadObjectList()) {
				$this->setError($this->_db->getError());
		}		
		return $this->_db->loadObjectList();
	}
	// add new
	public function addCountry($name, $order = 1, $status = 1){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_country(name, `order`, status, updated) values ('.
					$db->Quote($name).','.
					$db->Quote($order).','.
					$db->Quote($status).','.
					'CURDATE())';
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//edit
	public function editCountry($id, $name, $order = 1, $status = 1){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_country SET name = '.$db->Quote($name).', `order`= '.$db->Quote($order).', status = '.$db->Quote($status).', updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//delete
	public function deleteCountry($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_country WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
//-- -----------------------------------------------------
//-- 4. Table `#__retailshop_city`
//-- -----------------------------------------------------
	public function getArrCity($id){
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_city as h')
			->select('*')
			->where('status = 1 AND h.country_id =' . $id));
		if (!$this->_db->loadObjectList()) {
				$this->setError($this->_db->getError());
		}		
		return $this->_db->loadObjectList();
	}
	//add new city
	public function addCity($name, $order = 1, $status = 1, $country_id){				
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_city(name, `order`, status, country_id, updated) values ('.
					$db->Quote($name).','.
					$db->Quote($order).','.
					$db->Quote($status).','.
					$db->Quote($country_id).','.
					'CURDATE())';
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	// edit city
	public function editCity($id, $name, $order = 1, $status = 1, $country_id){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_city SET name = '.$db->Quote($name).', `order`= '.$db->Quote($order).', status = '.$db->Quote($status).', country_id = '.$db->Quote($country_id).',updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//delete
	public function deleteCity($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_city WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
//-- -----------------------------------------------------
//-- 5. Table `#__retailshop_user`
//-- -----------------------------------------------------	
	//select user
	public function getUser($id){
		$db =& JFactory::getDBO();
		$query = 'SELECT * FROM #__retailshop_user AS h WHERE h.status = 1 AND h.id_user =' . $id;
		$db->setQuery($query);
		$id = $db->loadObject();		
		return $id->id;
	}
	//add new user
	public function addUser($firstname, $lastname, $email , $birthdate, $id_user, $status){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_user(firstname, lastname, email, birthdate, id_user, status, date_register, updated) values ('.
					$db->Quote($firstname).','.
					$db->Quote($lastname).','.
					$db->Quote($email).','.
					$db->Quote($birthdate).','.
					$db->Quote($id_user).','.
					$db->Quote($status).','.
					'CURDATE(), CURDATE())';
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	// edit user
	public function editUser($firstname, $lastname, $email , $birthdate, $id_user, $status){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_user SET firstname = '.$db->Quote($firstname).', lastname = '.$db->Quote($lastname).', email = '.$db->Quote($email).', birthdate = '.$db->Quote($birthdate).', status = '.$db->Quote($status).',updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}	
	//delete
	public function deleteUser($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_user WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}

//-- -----------------------------------------------------
//-- 6. Table `#__retailshop_shop`
//-- -----------------------------------------------------	
	public function getallshop($limit = 0){
		$db =& JFactory::getDBO();
		$li = $limit!=0 ? ' LIMIT 0,'.$limit : '';
		$query = 'SELECT * FROM #__retailshop_shop WHERE status = 1 ORDER BY updated'.$li;
		$db->setQuery($query);	
		return $db->loadObjectList();
	}
	public function getallshopbyUser($id){
		$db =& JFactory::getDBO();
		$query = 'SELECT a.id, a.name, a.title, a.short_description, a.logo, a.address	FROM #__retailshop_shop AS a INNER JOIN #__retailshop_user AS b ON a.retailshop_user_id = b.id WHERE a.status = 1 AND b.id_user = '.$db->Quote($id).' ORDER BY a.updated DESC';
		$db->setQuery($query);
		return $db->loadObjectList();
	}
	public function getShopbycity($city_id){
		$db =& JFactory::getDBO();
		$query = 'SELECT * FROM #__retailshop_shop AS a INNER JOIN #__retailshop_city_shop AS b ON a.id = b.shop_id INNER JOIN #__retailshop_city AS c ON b.city_id = c.id WHERE a.status = 1  AND c.id = '.$city_id.' ORDER BY `order`,updated';
		$db->setQuery($query);	
		return $db->loadObjectList();
	}
		
	//select user
	public function getShop($id){		
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_shop as h')
			->select('*')
			->where('status =1 AND h.id =' . (int)$id));
		if (!$this->_db->loadObject()) {
			$this->setError($this->_db->getError());
		}			
		return $this->_db->loadObject();
	}
	//add new user
	public function addShop($name, $title, $logo , $link, $address, $short_description, $description, $email, $image, $isgooglemap = 0, $latitude = null, $longitude = null, $address_googlemap = '', $title_googlemap = '', $content_googlemap = '', $category_id, $retailshop_user_id){
		
		$db =& JFactory::getDBO();
		
		$query = 'INSERT INTO #__retailshop_shop (`name`, `title`, `logo`, `link`, `address`, `short_description`, `description`, `email`, `date_created`, `image`, `isgooglemap`, `latitude`, `longitude`, `address_googlemap`, `title_googlemap`, `content_googlemap`, `status`, `order`, `updated`, `category_id`, `retailshop_user_id`) VALUES  ('.
					$db->Quote($name).','.$db->Quote($title).','.$db->Quote($logo).','.
					$db->Quote($link).','.$db->Quote($address).','.$db->Quote($short_description).','.
					$db->Quote($description).','.$db->Quote($email).', CURDATE() ,'.$db->Quote($image).','.
					$db->Quote($isgooglemap).','.$db->Quote($latitude).','.$db->Quote($longitude).','.
					$db->Quote($address_googlemap).','.$db->Quote($title_googlemap).','.
					$db->Quote($content_googlemap).', 1, 1,CURDATE(),'.$db->Quote($category_id).','.
					$db->Quote($retailshop_user_id).')';
		
		$db->setQuery($query);		
		if($db->query()){
			$query = 'SELECT * FROM #__retailshop_shop AS s WHERE `email` = '.$db->Quote($email).' LIMIT 0,1';
			$db->setQuery($query);
			$id = $db->loadObject();
			return $id->id;			
		} else {		
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	// edit
	public function editShop($id,$name, $title, $logo , $link, $address, $short_description, $description, $email, $image, $isgooglemap = 0, $latitude = null, $longitude = null, $address_googlemap = '', $title_googlemap = '', $content_googlemap = '', $status = 1, $order = 1, $category_id){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_shop SET name = '.$db->Quote($name).', title = '.$db->Quote($title).', logo = '.$db->Quote($logo).', link = '.$db->Quote($link).', address = '.$db->Quote($address).', short_description = '.$db->Quote($short_description).', description = '.$db->Quote($description).', email = '.$db->Quote($email).', image = '.$db->Quote($image).', isgooglemap = '.$db->Quote($isgooglemap).', latitude = '.$db->Quote($latitude).', longitude = '.$db->Quote($longitude).', address_googlemap = '.$db->Quote($address_googlemap).', title_googlemap = '.$db->Quote($title_googlemap).', content_googlemap = '.$db->Quote($content_googlemap).', status = '.$db->Quote($status).', `order`= '.$db->Quote($order).', category_id = '.$db->Quote($category_id).', updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}	
	//delete
	public function deleteShop($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_shop WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}

//-- -----------------------------------------------------
//-- 7. Table `#__retailshop_event`
//-- -----------------------------------------------------	
	
	public function getArrEventAllShop($arr = null){
			$db =& JFactory::getDBO();
			if(!is_array($arr)){
				$query = 'SELECT * FROM `#__retailshop_event` WHERE end_date > CURDATE() AND status = 1';
				$db->setQuery($query);				
				return $db->loadObjectList();
			}else{
				$query = 'SELECT * FROM `#__retailshop_event` WHERE id IN (';
				for($i=0; $i<count($arr); $i++){					
					$query .= ($i==0)?$arr[$i]:','.$arr[$i];					
				}
				$query .= ')';			
								
				$db->setQuery($query);				
				return $db->loadObjectList();
			}
	}
	
	public function getArrEvent($id_shop, $limit = '10', $page = 0){
			$db =& JFactory::getDBO();
			$query = 'SELECT * FROM `#__retailshop_event` WHERE `status` = 1 AND `shop_id` = '.$id_shop.' ORDER BY updated limit ' . $limit*$page . ',' . $limit;
			$db->setQuery($query);			
			return $db->loadObjectList();
	}
    
    public function getArrAllEvent($limit = '10'){
			$db =& JFactory::getDBO();
			$query = 'SELECT * FROM `#__retailshop_event` WHERE `status` = 1 ORDER BY `order`,updated limit 0,'.$limit;			
			$db->setQuery($query);			
			return $db->loadObjectList();
	}
    
	//select a event
	public function getEvent($id){
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_event as h')
			->select('*')
			->where('status = 1 AND h.id =' . $id));
		if (!$this->_db->loadObject()) {
				$this->setError($this->_db->getError());
		}		
		return $this->_db->loadObject();
	}
	//add
	public function addEvent($title, $content, $fontend , $order, $start_date, $end_date, $status, $shop_id){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_event(title,content,fontend,`order`,start_date,end_date,status, shop_id, updated) VALUES ('.								
								$db->Quote($title).','.
								$db->Quote($content).','.
								$db->Quote($fontend).','.
								$db->Quote($order).','.
								$db->Quote($start_date).','.
								$db->Quote($end_date).','.
								$db->Quote($status).','.								
								$db->Quote($shop_id).','.								
								'CURDATE())';			
		$db->setQuery($query);							
		if($db->query()){												
			return true;
		} else {						
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	// edit
	public function editEvent($title, $content, $fontend , $order, $start_date, $end_date, $status, $shop_id, $product_id){
		$db =& JFactory::getDBO();
		$query = 'UPDATE  #__retailshop_event SET'.							 
							 ' title = '.$db->Quote($title).
							 ' ,content = '.$db->Quote($content).
							 ' ,fontend = '.$db->Quote($fontend).
							 ' ,`order` = '.$db->Quote($order).
							 ' ,start_date = '.$db->Quote($start_date).
							 ' ,end_date = '.$db->Quote($end_date).
							 ' ,status = '.$db->Quote($status).
							 ' ,shop_id = '.$db->Quote($shop_id).
							 ' ,updated = CURDATE()'.
							 ' WHERE id = '.$db->Quote($product_id);
		$db->setQuery($query);
		if($db->query()){						
			return true;
		} else {						
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//delete
	public function deleteEvent($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_event WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	
//-- -----------------------------------------------------
//-- 8. Table `#__retailshop_category_product`
//-- -----------------------------------------------------	
	//select
	public function getCategoryProduct($id){
		$db =& JFactory::getDBO();
		$query = "SELECT * FROM #__retailshop_category_product AS h WHERE h.status = 1 AND h.retailshop_shop_id = ". $id;
		$db->setQuery($query);
		return $db->loadObjectList();
	}
	public function getCategoryProductbyid($id){
		$db =& JFactory::getDBO();
		$query = "SELECT * FROM #__retailshop_category_product AS h WHERE h.status = 1 AND h.id = ". $id;
		$db->setQuery($query);
		return $db->loadObject();
	}
    
    public function getCategoryMenuOfShop($id){
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_category_product as h')
			->select('*')
			->where('h.status = 1 AND h.is_menu != 0 AND h.retailshop_shop_id =' . $id));
		if (!$this->_db->loadObjectList()) {
				$this->setError($this->_db->getError());
		}		
		return $this->_db->loadObjectList();
	}
	//add 
	public function addCategoryProduct($id_parent= null, $is_menu, $name , $order, $status, $retailshop_shop_id){
		$db =& JFactory::getDBO();		
		$query = 'INSERT INTO #__retailshop_category_product(id_parent, is_menu, name, `order`, status, retailshop_shop_id, updated) values ('.
					$db->Quote($id_parent).','.
					$db->Quote($is_menu).','.
					$db->Quote($name).','.
					$db->Quote($order).','.
					$db->Quote($status).','.
					$db->Quote($retailshop_shop_id).','.
					'CURDATE())';							
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	// edit
	public function editCategoryProduct($id ,$id_parent, $is_menu, $name , $order, $status, $retailshop_shop_id){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_category_product SET id_parent = '.$db->Quote($id_parent).', is_menu = '.$db->Quote($is_menu).', name = '.$db->Quote($name).', `order`= '.$db->Quote($order).', status = '.$db->Quote($status).', retailshop_shop_id = '.$db->Quote($retailshop_shop_id).',updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	public function savecate($shop = 0){		
		$id_parent =  JRequest::getVar('start-catagory')== -1 ? 0 : JRequest::getVar('start-catagory');
		$is_menu =  JRequest::getVar('menu');
		$name =  JRequest::getVar('cat-name');
		$action =  JRequest::getVar('action');		
		$id =  JRequest::getVar('id');		
		$shop = $_SESSION['shopid'];		
		switch($action){
			case 'addnew':
				$this->addCategoryProduct($id_parent, $is_menu, $name , 1, 1, $shop);
				break;
			case 'edit':
				$this->editCategoryProduct($id ,$id_parent, $is_menu, $name , 1, 1, $shop);
			default:
				break;
		}
		
	}
	
	//delete
	public function deleteCategoryProduct($id){
		$db =& JFactory::getDBO();
		$arr = $this->productbycatelogy($id);
		if(count($arr)==0){
			$query = 'DELETE FROM #__retail_shop_category_related_product WHERE retailshop_category_product_id = '.$id;
			$db->setQuery($query); $db->query();
			$query = 'DELETE FROM #__retailshop_category_product WHERE id = '.$id;
			$db->setQuery($query);				
			if($db->query()){						
				return true;
			} else {					
				$this->last_error = $db->getErrorMsg();
				return false;
			}
		}
		echo "<script>alert('Careful! Have products in this catelory')</script>";
		return false;
	}	

//-- -----------------------------------------------------
//-- 9. Table `#__retailshop_product`
//-- -----------------------------------------------------	
	
	public function getProduct($id){
			$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_product as p')
			->select('p.id, p.name, p.title, p.short_description, p.description, p.price, p. promotion, p.date_end_promotion, p.date_start_promotion ,p.date_end_promotion - p.date_start_promotion AS timetime, `date_end_promotion` - CURRENT_TIMESTAMP() AS deal, p.image_url, p.free_gift, p.shop_id, p.status, p.`order`')
			->where('status = 1 AND p.id =' . $id));
			if ($this->_db->loadObjectList()){
				$this->setError($this->_db->getError());
			}
			return $this->_db->loadObject();
	}
	//select
	public function getArrProducts($id_shop,$limit = 0,$deals = false, $page = 0,$all = false){
			$db =& JFactory::getDBO();
			$getdeals = ($deals==true)?' date_end_promotion > CURRENT_TIMESTAMP() AND ':' date_end_promotion < CURRENT_TIMESTAMP() AND ';
			if($all == true) $getdeals = ' 1 AND ';
			$getlimit = ($limit > 0)?' LIMIT '.$page*$limit .','.$limit : '';
			$query = 'SELECT a.id, a.name, a.title, a.short_description, a.description, a.price, a.promotion, a.date_end_promotion - a.date_start_promotion AS timetime, a.image_url, a.order, a.free_gift, a.shop_id FROM `#__retailshop_product` AS a WHERE '.$getdeals.'status = 1 AND `shop_id` = '.(int)$id_shop.' ORDER BY `updated`'.$getlimit;			
			$db->setQuery($query);				
			return $db->loadObjectList();			
	}
	public function listalldeal($limit = 0){
			$db =& JFactory::getDBO();			
			$getlimit = ($limit > 0)?' LIMIT '.$limit*9 .',9' : '';
			$query = 'SELECT a.id, a.name, a.title, a.short_description, a.description, a.price, a.promotion, a.date_end_promotion - a.date_start_promotion AS timetime, a.image_url, a.order, a.free_gift, a.shop_id FROM `#__retailshop_product` AS a WHERE `date_end_promotion` > CURRENT_TIMESTAMP() AND status = 1 ORDER BY `updated`'.$getlimit;			
			$db->setQuery($query);				
			return $db->loadObjectList();			
	}	
	//add
	public function addProduct($name, $title , $short_description, $description, $price, $promotion, $date_start_promotion, $date_end_promotion, $image_url, $order, $free_gift , $status, $shop_id){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_product(`name`, `title`, `short_description`, `description`, `price`, `promotion`, `date_start_promotion`, `date_end_promotion`, `image_url`, `order`, `free_gift`, `status`, `shop_id`) values ('.
								$db->Quote($name).','.
								$db->Quote($title).','.
								$db->Quote($short_description).','.
								$db->Quote($description).','.
								$db->Quote($price).','.
								$db->Quote($promotion).','.
								$db->Quote($date_start_promotion).','.
								$db->Quote($date_end_promotion).','.
								$db->Quote($image_url).','.
								$db->Quote($order).','.
								$db->Quote($free_gift).','.
								$db->Quote($status).','.
								$db->Quote($shop_id).')';				
		$db->setQuery($query);	
		if($db->query()){						
			$query = 'SELECT s.id FROM #__retailshop_product AS s ORDER BY s.id DESC LIMIT 0,1';
			$db->setQuery($query);
			$id = $db->loadObject();
			return $id->id;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}		
	}
	// edit
	public function editProduct($name, $title , $short_description, $description, $price, $promotion, $date_start_promotion, $date_end_promotion, $image_url='', $order, $free_gift , $status, $product_id){
		$db =& JFactory::getDBO();
		$image = ($image_url!='')?$image_url : '';
		$query = 'UPDATE  #__retailshop_product SET'.
							 ' name = '.$db->Quote($name).
							 ' ,title = '.$db->Quote($title).
							 ' ,short_description = '.$db->Quote($short_description).
							 ' ,description = '.$db->Quote($description).
							 ' ,price = '.$db->Quote($price).
							 ' ,promotion = '.$db->Quote($promotion).
							 ' ,date_start_promotion = '.$db->Quote($date_start_promotion).
							 ' ,date_end_promotion = '.$db->Quote($date_end_promotion);
		if($image_url!='')
			$query .= ' ,image_url = '.$db->Quote($image_url);
		$query .= ' ,`order` = '.$db->Quote($order).
							 ' ,free_gift = '.$db->Quote($free_gift).
							 ' ,status = '.$db->Quote($status).							 
							 ' WHERE id = '.$db->Quote($product_id);
		
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {						
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//delete
	public function deleteProduct($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_comment WHERE product_id = '.$id;
		$db->setQuery($query); $db->query();
		$query = 'DELETE FROM #__retailshop_related_product WHERE product_id = '.$id;
		$db->setQuery($query);	$db->query();	
		$query = 'DELETE FROM #__retailshop_rating_product WHERE product_id = '.$id;
		$db->setQuery($query); $db->query() ;
		$query = 'DELETE FROM #__retail_shop_category_related_product WHERE retailshop_product_id = '.$id;
		$db->setQuery($query); $db->query() ;
		$query = 'DELETE FROM #__retailshop_product WHERE id = '.$id;
		$db->setQuery($query);
		
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}	
	
//-- -----------------------------------------
//-- 10. TABLE __retailshop_related_product 
//-- -----------------------------------------
	public function getArrRelatedProduct($id){
		$db =& JFactory::getDBO();
		
		$query = 'SELECT a.* FROM #__retailshop_product AS a INNER JOIN #__retailshop_related_product AS b ON a.id = b.product_related_id WHERE b.product_id ='.$id;
		
		$db->setQuery($query);		
		return $db->loadObjectList();		
	}
	//add
	public function addRelatedProduct($product_id, $product_related_id, $order = 1, $status = 1){				
		$db =& JFactory::getDBO();
		$query = "SELECT COUNT(*) AS sl FROM #__retailshop_related_product WHERE product_id = ".$db->Quote($product_id)." AND product_related_id= ".$db->Quote($product_related_id);
		$db->setQuery($query);		
		if($db->query()){			
			$row = $db->loadObject();			
			if($row->sl == 0){
				$query = 'INSERT INTO #__retailshop_related_product(`order`, `status`, `product_related_id`, `product_id`, `updated`) values ('.					
					$db->Quote($order).','.
					$db->Quote($status).','.
					$db->Quote($product_related_id).','.
					$db->Quote($product_id).', CURDATE())';		
					$db->setQuery($query);									
				if($db->query()){						
					return true;
				} else {					
					$this->last_error = $db->getErrorMsg();
					return false;
				}
			}			
		} else {			
			$this->last_error = $db->getErrorMsg();
			return false;
		}
		
		
		
	}
	// edit city
	public function editRelatedProduct($id, $order = 1, $status = 1, $product_id){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_related_product SET `order`= '.$db->Quote($order).', status = '.$db->Quote($status).', product_id = '.$db->Quote($product_id).',updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);
		if($db->query()){				
			return true;
		} else {			
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//delete
	public function deleteRelatedProduct($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_related_product WHERE product_id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}	
	public function deleteProductRelated($id, $idre){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_related_product WHERE product_id = '.$id.' AND product_related_id = '.$idre;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}	
//-- -----------------------------------------
	//-- 11. TABLE Slideshow 
	//-- -----------------------------------------
	public function getArrSlideshow($shop_id){
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_slideshow as h')			
			->select('*')
			->where('shop_id = '.$shop_id));
		if (!$this->_db->loadObjectList()) {
				$this->setError($this->_db->getError());
		}		
		return $this->_db->loadObjectList();
	}
	public function getSlideshow($id){
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_slideshow as h')			
			->select('*')
			->where('id = '.$id));
		if (!$this->_db->loadObject()) {
				$this->setError($this->_db->getError());
		}		
		return $this->_db->loadObject();
	}
	// add new
	public function addSlideshow($title, $content, $link, $image_url, $fontend, $order, $status, $shop_id){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_slideshow(title,content,link,image_url,fontend,`order`,status,shop_id,updated) values ('.
					$db->Quote($title).','.
					$db->Quote($content).','.
					$db->Quote($link).','.
					$db->Quote($image_url).','.
					$db->Quote($fontend).','.
					$db->Quote($order).','.
					$db->Quote($status).','.
					$db->Quote($shop_id).','.
					'CURDATE())';
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//edit
	public function editSlideshow(){
		$db =& JFactory::getDBO();
		$slideimage = JRequest::getVar('slideimage', null, 'FILES', 'array');	
		$slidetitle = JRequest::getVar('slidetitle');
		$slidecontent = JRequest::getVar('slidecontent');
		$slidelink = JRequest::getVar('slidelink');
		$id = JRequest::getVar('slideid');
		$path =  substr(__file__, 0, strpos(__file__, "components")) . "images". DS ."com_retailshop";
		
		$query = 'UPDATE #__retailshop_slideshow SET title = '.$db->Quote($slidetitle[0]).', content = '.$db->Quote($slidecontent[0]).', link = '.$db->Quote($slidelink[0]);
		
		if($slideimage['name'][0] != ''){
				if($this->check_extent_file($slideimage['name'][0],"jpg|gif|png|JPG|GIF|PNG") ){							
					$file_input_tmp = $slideimage['tmp_name'][0];
					$prefix = time().'_';
					$logoname = $prefix.$slideimage['name'][0];					
					
					move_uploaded_file( $file_input_tmp, $path.DS.$logoname);					
					$query .= ', image_url = '.$db->Quote($logoname);
				}
			}
		$query .= ', updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//delete
	public function deleteSlideshow($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_slideshow WHERE id = '.$id;
		$db->setQuery($query);	
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}	
	 
	
//-- -----------------------------------------
	//-- 12. TABLE Address City Shop
	//-- -----------------------------------------
	public function getArrCityShop($ctry, $shop){
		$this->_db->setQuery($this->_db->getQuery(true)
			->from('#__retailshop_city_shop as h')
			->select('*')
			->where('h.city_id =' . $ctry.' AND h.shop_id ='.$shop));
		if (!$this->_db->loadObjectList()) {
				$this->setError($this->_db->getError());
		}		
		return $this->_db->loadObjectList();
	}
	
	public function addCityShop($address, $city_id, $shop_id, $cityname){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_city_shop(address, city_id, shop_id, `order`, status, updated, city) values ('.$db->Quote($address).','.$db->Quote($city_id).','.$db->Quote($shop_id).', 1, 1, CURDATE(),'.$db->Quote($cityname).')';		
		$db->setQuery($query);
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	
	// edit city
	public function editCityShop($name, $order = 1, $status = 1, $country_id, $shop_id, $id){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_city_shop SET name = '.$db->Quote($name).', `order`= '.$db->Quote($order).', status = '.$db->Quote($status).', country_id = '.$db->Quote($country_id).', shop_id = '.$db->Quote($shop_id).',updated = CURDATE() WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//delete
	public function deleteCityShop($id){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retailshop_city_shop WHERE id = '.$id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}		

//-- ----------------------------------------
//-- 13. TABLE __retailshop_brand_shop 
//-- -----------------------------------------	
	// add new
	public function addBrandShop($brand_id, $shop_id){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_brand_shop(status,brand_id,shop_id,updated) values (1, '.
				$db->Quote($brand_id).','.$db->Quote($shop_id).', CURDATE())';					
		$db->setQuery($query);
		if($db->query()){				
			return true;
		} else {			
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//edit
	public function editBrandShop($status = 1, $brand_id, $shop_id){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_brand_shop SET status = '.$db->Quote($status).', brand_id = '.$db->Quote($brand_id).', shop_id = '.$db->Quote($shop_id).', updated = CURDATE() WHERE brand_id = '.$band_id.' AND shop_id = '.$shop_id;
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}

//-- ----------------------------------------
//-- 13. TABLE __retailshop_comment
//-- -----------------------------------------	
	// add new
	public function addComment($title, $email, $name, $content, $product_id ){
		$db =& JFactory::getDBO();
		$query = 'INSERT INTO #__retailshop_comment(title,email,name,content,`order`,status,product_id,updated) values ('.
					$db->Quote($title).','.
					$db->Quote($email).','.
					$db->Quote($name).','.
					$db->Quote($content).',1,1,'.				
					$db->Quote($product_id).','.
					'CURDATE())';							
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	//edit _retail_shop_category_related_product
	public function listcomment($id){
		$db =& JFactory::getDBO(); 
		$query = 'SELECT * FROM #__retailshop_comment WHERE product_id = '.$db->Quote($id);
		$db->setQuery($query);
		return $db->loadObjectList();
	}

//-- ----------------------------------------
//-- 13. TABLE _retail_shop_category_related_product
//-- -----------------------------------------	
	// add new
	public function category_related_product($category, $product){
		$db =& JFactory::getDBO();
		
		$query = 'SELECT COUNT(*) as sl FROM #__retail_shop_category_related_product WHERE `retailshop_product_id`='.$db->Quote($product).' AND `retailshop_category_product_id`='.$db->Quote($category);
		$db->setQuery($query);		
		if($db->query()){			
			$row = $db->loadObject();			
			if($row->sl == 0){			
			$query = 'INSERT INTO #__retail_shop_category_related_product(retailshop_product_id, retailshop_category_product_id) VALUES('.$db->Quote($product).','.$db->Quote($category).')';
			$db->setQuery($query);			
			if($db->query()){						
				return true;
			} else {					
				$this->last_error = $db->getErrorMsg();
				return false;
			}
		}		
		} else {			
			$this->last_error = $db->getErrorMsg();
			return false;
		}		
	}	
	
	public function getcategory_related_product($id){
		$db =& JFactory::getDBO();
		$query = 'SELECT a.id, a.retailshop_category_product_id FROM #__retail_shop_category_related_product AS a WHERE a.retailshop_product_id = '.$id;		
		$db->setQuery($query);		
		return $db->loadObjectList();
	}
	public function getcategory_product_related($idp, $idcate){
		$db =& JFactory::getDBO();
		$query = 'DELETE FROM #__retail_shop_category_related_product WHERE retailshop_product_id = '.$idp.' AND retailshop_category_product_id = '.$idcate;		
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
//---
//-- xu ly
//---
/*
	when btnSaveProduct click
		- Edit -> save
		- New  -> save
*/
	public function saveProduct($shop_id = 0){		
		try	{

			$action = getRequest('action');
						
			//get value
			$name = getRequest('pro-name');			
			
			$title = getRequest('pro-title');
			$short_description = getRequest('pro-short-description');
			$description = getRequest('pro-description');
			$price = getRequest('pro-price');
			$promotion = getRequest('pro-promotion');
			$date_start_promotion = getRequest('start');
			$date_end_promotion = getRequest('end');			
			$free_gift = getRequest('pro-free-gift');
			$status = getRequest('pro-status')=='on' ? 1 : 0;
			$order = getRequest('pro-order');
			$related = JRequest::getVar('check');
			$categorys = JRequest::getVar('checkcate'); 
			$file = JRequest::getVar('pro-image', null, 'FILES', 'array');	
			$image_url = '';
			//path physical
			$path =  substr(__file__, 0, strpos(__file__, "components")) . "images". DS ."com_retailshop";
						
			//save file to folder media
			if($file['name'] != ''){
				if($this->check_extent_file($file['name'],"jpg|gif|png|JPG|GIF|PNG") ){							
					$file_input_tmp = $file['tmp_name'];
					$prefix = time().'_';
					$image_url = $prefix.$file['name'];					
					
					move_uploaded_file( $file_input_tmp, $path.DS.$image_url);					
				}
			}			
			switch($action)
			{
				case 'addnew':
					//add product --> return id of product
					$id = $this->addProduct($name, $title , $short_description, $description, $price, $promotion, $date_start_promotion, $date_end_promotion, $image_url, $order, $free_gift , $status, $shop_id);
					
					//add relate product
					if(count($related) > 0){
						//$cate.. , $prod..						
						foreach($related as $idcate){							
							$this->addRelatedProduct($id, $idcate);
						}
					}

					//add relate catelogy
					if(count($categorys) > 0){
						foreach($categorys as $id_cate){							
							$this->category_related_product($id_cate, $id);
						}
					}
					break;
				case 'edit':					
					$product_id = getRequest('productid');
					$this->editProduct($name, $title , $short_description, $description, $price, $promotion, $date_start_promotion, $date_end_promotion, $image_url, $order, $free_gift , $status, $product_id);
					
					$relatedproduct = $this->getArrRelatedProduct($product_id);
					$catologproduct = $this->getcategory_related_product($product_id);
					
					//edit catelogy and relate product
					if(count($related) > 0){
						//$cate.. , $prod..					
						foreach($related as $idcate){
							$this->addRelatedProduct($product_id, $idcate);							
						}
						//delete						
						foreach($relatedproduct as $p){
							$count = 0;
							foreach($related as $idcate){								
								if($p->id == $idcate){
									$count++;
									continue;
								}
							}
							if($count==0){
								$this->deleteProductRelated($product_id, $p->id);
							}
						}
					}
					
					//add relate catelogy
					if(count($categorys) > 0){
						foreach($categorys as $idcate)
							$this->category_related_product($idcate, $product_id);
						
						//delete		
						foreach($catologproduct as $p){
							$count = 0;
							foreach($categorys as $idcate){								
								if($p->retailshop_category_product_id == $idcate){
									$count++;
									continue;
								}
							}
							if($count==0){								
								$this->getcategory_product_related($product_id, $p->retailshop_category_product_id);
							}
						}
						
					}
					break;
				default:
					return false;
			}	
		}
		catch(Exception $e)
		{
			return false;
		}				
	}
	
	public function saveEvent($shop_id){
		$db =& JFactory::getDBO();
		$action = getRequest('action');
		
		$title = getRequest('txtTitle');
		$content = getRequest('txtContent');
		$fontend = 1;
		$order = 1;
		$start_date = getRequest('txtStartEvent');
		$end_date = getRequest('txtEndEvent');				
		$status = 1;
		switch($action)
			{
				case 'addnew':					
					$this->addEvent($title, $content, $fontend , $order, $start_date, $end_date, $status, $shop_id);
					break;
				case 'edit':
					$product_id = getRequest('eventid');
					$this->editEvent($title, $content, $fontend , $order, $start_date, $end_date, $status, $shop_id, $product_id);
					break;
				default:
					return false;
			}
	}
	
	public function search(){
		$city_select = getRequest( 'city_select');
		$country_select = getRequest( 'country_select');
		$category_select = getRequest( 'category_select');
		$brand_select = getRequest( 'brand_select');
		$find_cityname = getRequest('find_cityname');
		$url = JURI::base().'images/com_retailshop/logo/';
		$query = 'SELECT DISTINCT shop.id, shop.title, shop.name, shop.link, shop.address, shop.short_description, shop.logo FROM `#__retailshop_shop` AS shop';
		
		if($brand_select != ''){			
			$query .=' INNER JOIN #__retailshop_brand_shop AS bs ON shop.id = bs.shop_id INNER JOIN #__retailshop_brand AS brand ON brand.id = bs.brand_id';
		}			
		if($city_select != -1)
			$query .= ' INNER JOIN #__retailshop_city_shop AS city ON shop.id = city.shop_id INNER JOIN #__retailshop_city AS rc ON city.city_id = rc.id';
		else
			if($country_select != -1)
				$query .= ' INNER JOIN #__retailshop_city_shop AS city ON shop.id = city.shop_id INNER JOIN #__retailshop_city AS rc ON city.city_id = rc.id INNER JOIN #__retailshop_country AS country ON country.id = rc.country_id';
			else
				if($find_cityname != '')
					$query .= ' INNER JOIN #__retailshop_city_shop AS city ON shop.id = city.shop_id INNER JOIN #__retailshop_city AS rc ON city.city_id = rc.id';
		if($category_select != -1)
			$query .= ' INNER JOIN #__retailshop_category AS cate ON cate.id = shop.category_id';
		
		$query .= ' WHERE ';
		if($brand_select != ''){
			$arr = explode(';',$brand_select);			
			$str = '(';
			for($i = 0; $i<count($arr); $i++){
				if($i==0)
					$str .= ' UPPER(brand.name) LIKE UPPER("%'.$arr[$i].'%")';
				else
					$str .= ' OR UPPER(brand.name) LIKE UPPER("%'.$arr[$i].'%")';
			}
			$str .=')';
			$query .= $str;
		}
		else 
			$query .= ' 1 ';
		if($city_select != -1)
			$query .= 'AND rc.id = '.$city_select;
		else
			if($country_select != -1)
				$query .= 'AND  country.id ='.$country_select;
		
		if($find_cityname != '')
			$query .= 'AND  UPPER(city.city) LIKE UPPER("%'.$find_cityname.'%")';
		if($category_select != -1)
			$query .= ' AND  cate.id ='.$category_select;		
		
		echo $query;
		$db =& JFactory::getDBO();
		
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
		$result = '';				
		if(count($rows)){
		foreach ( $rows as $row ) {
			$result .= '<div class="result-shop"><a href="index.php?option=com_retailshop&view=myshop&shop_id='.$row->id.'">
           	<img src="'.$url.$row->logo.'" width="70" height="70" /></a><a href="index.php?option=com_retailshop&view=myshop&shop_id='.$row->id.'">
            <p class="title">'.$row->title.'</p></a>
			</div>';
		} }else{
			$result .= '<div class="result-shop">No results found</div>';
		}
		
		return $result;
	}
	
	public function listdeals($limit = 0){
		$db =& JFactory::getDBO();		
		$query = 'SELECT a.id, a.name, a.title, a.short_description, a.price, a.promotion,a.date_start_promotion,a.date_end_promotion, a.date_end_promotion - a.date_start_promotion AS timetime, a.image_url,  a.free_gift, a.shop_id ,a.price - a.price*a.promotion/100 as sale FROM `#__retailshop_product` AS a WHERE `date_end_promotion` > CURRENT_TIMESTAMP() AND status = 1 ORDER BY `updated` LIMIT '. $limit*9 .',9';		
		$db->setQuery( $query );
		$rows = $db->loadObjectList();		
		return $rows;
	}
	
	public function getShopId($id_user){
		$db =& JFactory::getDBO();
		$query = 'SELECT s.id FROM #__retailshop_user AS u INNER JOIN #__retailshop_shop AS s ON u.id = s.retailshop_user_id WHERE u.id_user = '.$id_user;
		$db->setQuery( $query );
		$id = $db->loadObject();		
		return $id;
	}
	
	public function getShopDefaultCity($city = "1", $limit = 0){
		$db =& JFactory::getDBO();
		$li = $limit != 0 ? ' LIMIT 0,'.$limit : '';
		$query = 'SELECT DISTINCT a.id, a.name, a.link, a.short_description, a.logo, a.title, a.address FROM #__retailshop_shop AS a INNER JOIN #__retailshop_city_shop AS b ON a.id = b.shop_id INNER JOIN #__retailshop_city AS c ON b.city_id = c.id WHERE a.status = 1  AND c.id = ((SELECT d.value FROM #__retailshop_config AS d WHERE d.name = "Default City '.$city.'"))'.$li;
		$db->setQuery($query);	
		return $db->loadObjectList();
	}
	
	public function getShopByCatelogy($id){
		$db =& JFactory::getDBO();
		$query ='SELECT a.id,a.name, a.title, a.link, a.address, a.logo, a.short_description FROM `#__retailshop_shop` as a INNER JOIN #__retailshop_category as b ON a.category_id = b.id WHERE b.id = '.$id;
		$db->setQuery($query);	
		return $db->loadObjectList();
	}	
	public function productbycatelogy($id, $limit = 0){
		$db =& JFactory::getDBO();
		$li = $limit == 0 ? '' : ' LIMIT '. $limit*9 .','. $limit;
		$query = 'SELECT DISTINCT a.id, a.name, a.title, a.short_description, a.price, a.promotion, a.image_url,`date_end_promotion` - CURRENT_TIMESTAMP() AS deal, a.date_end_promotion - a.date_start_promotion AS timetime, a.price - a.price*a.promotion/100 as sale 
				FROM #__retailshop_product AS a
				INNER JOIN #__retail_shop_category_related_product AS b ON a.id = b.retailshop_product_id
				INNER JOIN #__retailshop_category_product AS c ON b.retailshop_category_product_id = c.id
				WHERE c.id ='.$id;		
		$db->setQuery($query);									
		return $db->loadObjectList();
	}
    
	public function getproductpage($id_shop,$start = 0,$end = 0,$deals = false){
			$db =& JFactory::getDBO();
			$getdeals = ($deals==true)?' date_end_promotion > CURRENT_TIMESTAMP() AND ':'';			
			$getlimit = ($end > 0)?' LIMIT '.$start*$end.','.$end : '';
			$query = 'SELECT a.id, a.name, a.title, a.short_description, a.description, a.price, a.promotion, a.date_end_promotion - a.date_start_promotion AS timetime, a.image_url, a.order, a.free_gift, a.shop_id FROM `#__retailshop_product` AS a WHERE '.$getdeals.'status = 1 AND `shop_id` = '.(int)$id_shop.' ORDER BY `updated`'.$getlimit;			
			$db->setQuery($query);				
			return $db->loadObjectList();			
	}
	public function getnamecity($id){
		$db =& JFactory::getDBO();
		$query = 'SELECT * FROM `#__retailshop_city` WHERE id = ((SELECT value FROM #__retailshop_config WHERE name = "Default City '.$id.'"))';
		$db->setQuery($query);				
		return $db->loadObject();			
	}
	
	public function saveShop(){
		//var_dump($_POST);
		try{
		//$path =  substr(__file__, 0, strpos(__file__, "models")) . "media";
		$path =  substr(__file__, 0, strpos(__file__, "components")) . "images". DS ."com_retailshop";		
		$name = getRequest('start-name');	
		$title = getRequest('start-title');
		$address = getRequest('start-address');
		$link = getRequest('start-link');
		$short_description = getRequest('start-short-description');
		$description = getRequest('start-description');
		$email = getRequest('start-email');
		$brand = getRequest('start-brand');
		$category_id = getRequest('start-catelogy');
		//google info
		$google_map = JRequest::getVar('google-map')=='on' ? 1 : 0;
		$latitude = getRequest('latitude');
		$longitude = getRequest('longitude');
		$google_address = getRequest('google-address');
		$google_title = getRequest('google-title');
		$google_content = getRequest('google-content');
		
		$adress_adress = JRequest::getVar('adress-address');			
		$adress_Country = JRequest::getVar('country_select');
		$adress_City = JRequest::getVar('city_select');				
		$address_cityname = JRequest::getVar('txtcity');
		if($_SESSION['__default']['user']->id != 0){
			$retailshop_user_id = $this->getUser($_SESSION['__default']['user']->id);			
			//-----------------------
			// 1. save image, slide and logo		
			//---
			//1.1 save logo
			$logo = JRequest::getVar('start-logo', null, 'FILES', 'array');	
			$logoname = '';
			if($logo['name'] != ''){
				if($this->check_extent_file($logo['name'],"jpg|gif|png|JPG|GIF|PNG") ){							
					$file_input_tmp = $logo['tmp_name'];
					$prefix = time().'_';
					$logoname = $prefix.$logo['name'];					
					
					move_uploaded_file( $file_input_tmp, $path.DS.'logo'.DS.$logoname);
				}
			}
			//1.2 save image
			$image = JRequest::getVar('start-image', null, 'FILES', 'array');	
			$imagename = '';
			if($image['name'] != ''){
				if($this->check_extent_file($image['name'],"jpg|gif|png|JPG|GIF|PNG") ){	
					$file_input_tmp = $image['tmp_name'];
					$prefix = time().'_';
					$imagename = $prefix.$image['name'];					
					
					move_uploaded_file( $file_input_tmp, $path.DS.$imagename);					
				}
			}
			
			$slidetitle = JRequest::getVar('slidetitle');
			$slidecontent = JRequest::getVar('slidecontent');
			$slidelink = JRequest::getVar('slidelink');
			$slidefontend = JRequest::getVar('slidefontend');			
			
			$action = '';
			if(isset($_POST['action']))
				$action = JRequest::getVar('action');
			
			if($action!=''){
				//update info general
				$idshop = JRequest::getVar('idshop');
				$this->saveshop_general($idshop, $name, $title, $link, $short_description, $description, $email);
				//update google map				
				if($google_map){
					$this->saveshop_google_map($idshop, $google_map, $latitude, $longitude, $google_address, $google_title, $google_content);
				}
				//update logo - image
				if($logoname!=''){
					$this->saveshop_logo($idshop, $logoname);
				}
				if($imagename!=''){
					$this->saveshop_image($idshop, $imagename);
				}				
				$id_edit = JRequest::getVar('id_edit');	
				$num = JRequest::getVar('num');	
				for($i=0; $i<$num; $i++ ){			
					$this->saveshop_address($adress_adress[$i], $idshop, $adress_City[$i], $id_edit[$i],$address_cityname[$i]);
				}
					
				if($num<count($adress_adress)){
					for($i = $num; $i < count($adress_adress); $i++ ){			
						$this->addCityShop($adress_adress[$i], $adress_City[$i], $idshop, $address_cityname[$i]);
					}
				}
				
			}else{
				//1.3 upload slide
				$slideimage = JRequest::getVar('slideimage', null, 'FILES', 'array');
				
				
				//-----------------------
				// 2. save shop info
				//--- 
				$shop_id = $this->addShop($name, $title, $logoname , $link, $adress_adress[0], $short_description, $description, $email,$imagename, $google_map, $latitude, $longitude, $google_address, $google_title, $google_content, $category_id, $retailshop_user_id);		
				
				for($i=0; $i<count($slideimage['name']); $i++){
					if($slideimage['name'][$i] != ''){
						if($this->check_extent_file($slideimage['name'][$i],"jpg|gif|png|JPG|GIF|PNG") ){			
							$file_input_tmp = $slideimage['tmp_name'][$i];
							$prefix = time().'_';
							$namepic = $prefix.$slideimage['name'][$i];					
							
							move_uploaded_file( $file_input_tmp, $path.DS.$namepic);					
							$this->addSlideshow($slidetitle[$i], $slidecontent[$i], $slidelink[$i], $namepic, 1, 1, 1, $shop_id);
						}
					}
				}
				
				//-----------------------
				// 3. save address
				//--- 
				
				for($i=0; $i<count($adress_adress); $i++ ){			
					$this->addCityShop($adress_adress[$i], $adress_City[$i], $shop_id, $address_cityname[$i]);
				}

				//-----------------------
				// 4. save brand
				//--- 						
				if($brand != ''){
					$arrBrand = explode (";",$brand);			
					foreach($arrBrand as $b){						
						$brand_id = $this->addBrand($b);					
						$this->addBrandShop($brand_id, $shop_id);				
					}
				}			
			}
		}
		}
		catch(Exception $e)
		{
			return false;
		}
	}
	
	public function getdefaulftshop(){				
		$db =& JFactory::getDBO();		
		$query = 'SELECT value FROM #__retailshop_config WHERE name = "Default Shop"';
		$db->setQuery($query);
		$value =  $db->loadObject();			
		return $value->value;
	}
	
	public function saveslideshow(){
		$slideimage = JRequest::getVar('slideimage', null, 'FILES', 'array');	
		$slidetitle = JRequest::getVar('slidetitle');
		$slidecontent = JRequest::getVar('slidecontent');
		$slidelink = JRequest::getVar('slidelink');
		$path =  substr(__file__, 0, strpos(__file__, "components")) . "images". DS ."com_retailshop";		
		if(isset($_SESSION['shopid'])){
			$shop_id = $_SESSION['shopid'];
			for($i=0; $i<count($slideimage['name']); $i++){
				if($slideimage['name'][$i] != ''){
					if($this->check_extent_file($slideimage['name'][$i],"jpg|gif|png|JPG|GIF|PNG") ){			
						$file_input_tmp = $slideimage['tmp_name'][$i];
						$prefix = time().'_';
						$namepic = $prefix.$slideimage['name'][$i];					
						
						move_uploaded_file( $file_input_tmp, $path.DS.$namepic);					
						$this->addSlideshow($slidetitle[$i], $slidecontent[$i], $slidelink[$i], $namepic, 1, 1, 1, $shop_id);
					}
				}
			}
		}		
	}
	public function checkmaillink($link, $mail){
		$db =& JFactory::getDBO();		
		$query = 'SELECT * FROM #__retailshop_shop WHERE email = '.$mail.' AND link = '.$link;
		$db->setQuery($query);
		return count($db->loadObjectList());
	}
	
	public function checkusershop($id){
		$db =& JFactory::getDBO();		
		$query = 'SELECT a.id FROM #__retailshop_shop AS a INNER JOIN #__retailshop_user AS b ON a.retailshop_user_id = b.id WHERE b.id_user = '.$id.' LIMIT 0,1';
		$db->setQuery($query);
		$row = $db->loadObject();
		if (count($row)){
			return $row->id;
		}else{
			return 0;
		}
	}
	public function getShopmailbyproduct($idpro){
		$db =& JFactory::getDBO();		
		$query = 'SELECT b.email FROM #__retailshop_product AS a INNER JOIN #__retailshop_shop AS b ON a.shop_id = b.id WHERE a.id ='.$idpro.' LIMIT 0,1';
		$db->setQuery($query);
		$row = $db->loadObject();
		if (count($row)){
			return $row->email;
		}else{
			return null;
		}
	}
	public function getrandomshop(){
		$db =& JFactory::getDBO();		
		$query = 'SELECT * FROM #__retailshop_shop ORDER BY RAND() LIMIT 0 , 1';
		$db->setQuery($query);
		$row = $db->loadObject();
		return $row;
	}
	
	public function saveshop_general($id, $name, $title, $link, $short_description, $description, $email){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_shop SET name = '.$db->Quote($name).', `title`= '.$db->Quote($title).', link = '.$db->Quote($link).', short_description = '.$db->Quote($short_description).', email = '.$db->Quote($email).', description = '.$db->Quote($description).', updated = CURDATE() WHERE id = '.$id;		
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	public function saveshop_google_map($id, $isgooglemap, $latitude, $longitude, $address_googlemap, $title_googlemap, $content_googlemap){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_shop SET isgooglemap = '.$db->Quote($isgooglemap).', `latitude`= '.$db->Quote($latitude).', longitude = '.$db->Quote($longitude).', address_googlemap = '.$db->Quote($address_googlemap).', title_googlemap = '.$db->Quote($title_googlemap).', content_googlemap = '.$db->Quote($content_googlemap).', updated = CURDATE() WHERE id = '.$id;		
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	
	public function saveshop_logo($id, $logo){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_shop SET logo = '.$db->Quote($logo).', updated = CURDATE() WHERE id = '.$id;		
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	public function saveshop_image($id, $image){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_shop SET image = '.$db->Quote($image).', updated = CURDATE() WHERE id = '.$id;		
		$db->setQuery($query);									
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
	public function addressAllShop($id){
		$db =& JFactory::getDBO();
		$query = "SELECT b.id,b.city, a.name, b.address, c.name, d.name, a.id AS shop_id, b.id AS city_shop_id, c.id AS city_id, d.id AS country_id FROM `#__retailshop_shop` AS a INNER JOIN `#__retailshop_city_shop` AS b ON a.id = b.shop_id INNER JOIN `#__retailshop_city` AS c ON b.city_id = c.id INNER JOIN `#__retailshop_country` AS d ON c.country_id = d.id WHERE a.id = ".$id;
		$db->setQuery($query);
		$row = $db->loadObjectList();
		return $row;
	}
	public function saveshop_address($addname, $shop_id, $city_id, $id, $cityname){
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__retailshop_city_shop set address = '.$db->Quote($addname).', city_id = '.$db->Quote($city_id).', shop_id = '.$db->Quote($shop_id).', city = '.$db->Quote($cityname).' WHERE id= '.$id;
		$db->setQuery($query);		
		if($db->query()){						
			return true;
		} else {					
			$this->last_error = $db->getErrorMsg();
			return false;
		}
	}
}
?>